package com.aaa.sbms.dao;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;
import java.util.Map;

public interface RecruitDao {

    /**
     * 招聘信息
     * @return
     */
    @Select("select * from recruit_information")
    List<Map> getList();

    /**
     * 部门信息
     * @return
     */
    @Select("select * from depts where deptid != 5")
    List<Map> depts();

    /**
     * 根据部门名称查询部门编号
     * @param dname
     * @return
     */
    @Select("select deptid from depts where dname=#{dname}")
    int deptidByDname(String dname);

    /**
     * 职位信息
     * @return
     */
    @Select("select * from positions where pid !=2")
    List<Map> positions();

    /**
     * 根据职位名称查询职位编号
     * @param pname
     * @return
     */
    @Select("select pid from positions where pname=#{pname}")
    int pidByPname(String pname);

    /**
     * 分页查询招聘信息
     * @param map
     * @return
     */
    @Select("<script>select r.*,d.dname dname,p.pname pname from recruit_information r\n" +
            "left join depts d on d.deptid=r.deptid\n" +
            "left join positions p on p.pid=r.positionid\n" +
            "<where>" +
            "<if test=\"pname!=null\">and pname like '%${pname}%'</if>" +
            "</where>" +
            "order by number desc\n" +
            "limit #{begin},#{pageSize}</script>")
    List<Map>  getPage(Map map);

    /**
     * 分页查询应聘者信息
     * @param map
     * @return
     */
    @Select("<script>select r.*,d.dname dname,p.pname pname from recruit r\n" +
            "left join depts d on d.deptid=r.deptid\n" +
            "left join positions p on p.pid=r.positionid" +
            "<where>" +
            "r.status=1\n" +
            "<if test='ename!=null'> and ename like '%${ename}%'</if>"+
            "</where>limit #{begin},#{pageSize}</script>")
    List<Map> recruitPage(Map map);

    /**
     * 分页查询审核记录
     * @param map
     * @return
     */
    @Select("<script>select r.id,r.ename,r.sex,r.idcard,r.result,r.reason,date_format(r.time,'%Y-%m-%d %h:%i:%s') time," +
            "d.dname dname,p.pname pname from recruit_record r\n" +
            "left join depts d on d.deptid=r.deptid\n" +
            "left join positions p on p.pid=r.positionid<where>" +
            "<if test='ename!=null'> and ename like '%${ename}%'</if>"+
            "</where>order by time desc limit #{begin},#{pageSize}</script>")
    List<Map> recruitRecordPage(Map map);

    /**
     * 分页查询入职员工
     * @param map
     * @return
     */
    @Select("<script>select " +
            "userid,username,sex,date_format(locktime,'%Y-%m-%d %h:%i:%s') locktime,telephone,idcard,p.pname\n" +
            "from users u left join positions p on p.pid=u.positionid\n" +
            "<where><if test='username!=null'> and username like '%${ename}%'</if>"+
            "</where>order by locktime desc limit #{begin},#{pageSize}</script>")
    List<Map> empPage(Map map);

    /**
     * 查询招聘信息总数量
     * @param map
     * @return
     */
    @Select("<script>select count(id) from recruit_information r\n" +
            "left join depts d on d.deptid=r.deptid\n" +
            "left join positions p on p.pid=r.positionid\n" +
            "<where>" +
            "<if test=\"pname!=null\">and p.pname like '%${pname}%'</if>" +
            "</where></script>")
    int getPageCount(Map map);

    /**
     * 查询应聘者信息总数量
     * @param map
     * @return
     */
    @Select("<script>select count(*) from recruit r\n" +
            "left join depts d on d.deptid=r.deptid\n" +
            "left join positions p on p.pid=r.positionid\n" +
            "<where>" +
            "r.status=1\n" +
            "<if test='ename!=null'> and ename like '%${ename}%'</if>"+
            "</where></script>")
    int recruitPageCount(Map map);

    /**
     * 查询审核记录总数量
     * @param map
     * @return
     */
    @Select("<script>select count(*) from recruit_record r\n" +
            "left join depts d on d.deptid=r.deptid\n" +
            "left join positions p on p.pid=r.positionid\n" +
            "<where>" +
            "<if test='ename!=null'> and ename like '%${ename}%'</if>"+
            "</where></script>")
    int recruitRecordPageCount(Map map);

    /**
     * 查询入职员工总数量
     * @param map
     * @return
     */
    @Select("<script>select count(*) from users r <where>" +
            "<if test='username!=null'> and username like '%${ename}%'</if>"+
            "</where></script>")
    int empPageCount(Map map);

    /**
     * 查询一条招聘信息的数据
     * @return
     */
    @Select("select * from recruit_information where id=#{id}")
    Map recruitInfoOne(int id);

    /**
     * 查询一条应聘者信息
     * @return
     */
    @Select("select r.id,r.ename,r.filepath,d.dname,p.pname,r.idcard,r.phone,\n" +
            "r.graduation,r.education,date_format(r.time,'%Y-%m-%d %H:%i:%s') time,\n" +
            "r.sex,r.status\n" +
            "from recruit r\n" +
            "left join depts d on d.deptid=r.deptid\n" +
            "left join positions p on p.pid=r.positionid\n" +
            "where id=#{id}")
    Map recruitOne(int id);

    /**
     * 根据部门id和职位id查询招聘信息表对应的记录
     * @param deptid
     * @param positionid
     * @return
     */
    @Select("select id,number from recruit_information where deptid=#{deptid} and positionid=#{positionid}")
    Map findRecruitInfoOfNumberByDeptidAndPositionid(int deptid,int positionid);

    /**
     * 添加招聘信息
     * @param map
     * @return
     */
    @Insert("insert into recruit_information values(#{id},#{deptid},#{positionid},#{number},#{requir},#{duty},#{treat})")
    int addRecruitInfo(Map map);

    /**
     * 添加应聘者信息
     * @param map
     * @return
     */
    @Insert("insert into recruit values(null,#{ename},#{filepath},#{deptid},#{positionid},#{idcard},#{phone}," +
            "#{graduation},#{education},date_format(now(),'%Y-%m-%d %H:%i:%s'),#{sex},1)")
    int addRecruit(Map map);

    /**
     * 添加新员工到users表
     * @param map
     * @return
     */
    //idenamesexfilepathidcardphonegraduationeducationdnamepnamestatusreason
    @Insert("insert into users(username,password,filepath,telephone,sex,idcard,locktime,state,groupid,roleid,deptid,positionid,signday)\n" +
            "values(#{ename},123456,#{filepath},#{phone},#{sex},#{idcard}," +
            "date_format(now(),'%Y-%m-%d %H:%i:%s'),0,3,10,#{deptid},#{positionid},20)")
    int addUser(Map map);

//    @Insert("insert into checkins ")
//    int addDormitory(Map map);

    /**
     * 添加入职审核记录
     * @param map
     * @return
     */
    @Insert("insert into recruit_record values(null,#{ename},#{sex},#{deptid},#{positionid},#{idcard}," +
            "date_format(now(),'%Y-%m-%d %H:%i:%s'),#{result},#{reason})")
    int addRecord(Map map);

    /**
     * 更新入职申请表的状态为驳回2
     * @param id
     * @return
     */
    @Update("update recruit set status=2 where id=#{id}")
    int updateRecruitOfStatus3(int id);

    /**
     * 更新入职申请表的状态为驳回2
     * @param id
     * @return
     */
    @Update("update recruit set status=3 where id=#{id}")
    int updateRecruitOfStatus2(int id);

    /**
     * 更新招聘信息的所需人数-1
     * @param id
     * @return
     */
    @Update("update recruit_information set number=number-1 where id=#{id}")
    int updateRecruitInfoOfNumber(int id);

    /**
     * 更新招聘信息列表
     * @param map
     * @return
     */
    @Update("update recruit_information set deptid=#{deptid},positionid=#{positionid},number=#{number},requir=#{requir}," +
            "duty=#{duty},treat=#{treat} where id=#{id}")
    int updateRecruitInfo(Map map);

    /**
     * 更新新闻类型
     * @param map
     * @return
     */
    @Update("update newstype set newstype=#{newstype},intro=#{intro} where newstid=#{newstid}")
    int updateNewstype(Map map);

    /**
     * 更新新闻附件
     * @param map
     * @return
     */
    @Update("update news_accessory set newsid=#{newsid},accname=#{accname},accpath=#{accpath},accsize=#{accsize}" +
            " where accid=#{accid}")
    int updateNewsAcc(Map map);

    /**
     * 根据编号删除招聘信息
     * @param id
     * @return
     */
    @Delete("delete from recruit_information where id=#{id}")
    int deleteRecruitInfo(int id);

    /**
     * 根据编号删除新闻类型
     * @param newsid
     * @return
     */
    @Delete("delete from newstype where newstid=#{newstid}")
    int deleteNewstype(int newsid);

    /**
     * 根据编号删除新闻附件
     * @param accid
     * @return
     */
    @Delete("delete from news_accessory where accid=#{accid}")
    int deleteNewsAcc(int accid);
}
